import pandas as pd
import os

# 定义文件路径
working_directory = '/Users/linda/myprojects/gitee/MoneyDoubleSignal/2025-7-18最优/'
transactions_file = os.path.join(working_directory, 'transactions.csv')
positions_file = os.path.join(working_directory, 'positions.csv')
output_file = os.path.join(working_directory, 'df_turnover.csv')

# 读取数据
# 为transactions.csv的'symbol'列指定数据类型为str，避免被解析为其他类型
df_txn = pd.read_csv(transactions_file, dtype={'symbol': 'str'})
df_pos = pd.read_csv(positions_file)

# 将日期列转换为datetime对象
df_txn['date'] = pd.to_datetime(df_txn['date'])
df_pos['date'] = pd.to_datetime(df_pos['date'])

# 计算每日交易总额
df_txn['txn_value'] = (df_txn['amount'] * df_txn['price']).abs()
daily_txn_value = df_txn.groupby('date')['txn_value'].sum().reset_index()

# 计算每日投资组合总价值
# 将除'date'外的所有列相加
asset_columns = [col for col in df_pos.columns if col != 'date']
df_pos['total_value'] = df_pos[asset_columns].sum(axis=1)

# 提取日期和总价值
daily_portfolio_value = df_pos[['date', 'total_value']]

# 将前一天的投资组合总价值作为当日的期初总价值
daily_portfolio_value['beginning_value'] = daily_portfolio_value['total_value'].shift(1)

# 合并交易数据和投资组合价值数据
df_turnover = pd.merge(daily_txn_value, daily_portfolio_value, on='date', how='left')

# 计算换手率
# 避免除以0或NaN
df_turnover['Turnover'] = df_turnover.apply(
    lambda row: row['txn_value'] / row['beginning_value'] if row['beginning_value'] and row['beginning_value'] != 0 else 0,
    axis=1
)

# 准备输出的DataFrame
output_df = df_turnover[['date', 'Turnover']]

# 根据用户偏好，使用中英双语表头
output_df.rename(columns={'date': '日期(date)', 'Turnover': '换手率(Turnover)'}, inplace=True)

# 保存到CSV文件，不包含索引
output_df.to_csv(output_file, index=False, encoding='utf-8-sig')

print(f"每日换手率已成功计算并保存至 {output_file}")

# 计算并打印平均换手率
average_turnover = output_df['换手率(Turnover)'].mean()
print(f"总体的平均每日换手率是: {average_turnover:.4f}")

